﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace data_upload_HCollage
{
    public partial class Subject_Assign_Form : Form
    {
        public Subject_Assign_Form()
        {
            InitializeComponent();
        }

        private void Subject_Assign_Form_Load(object sender, EventArgs e)
        {
            cmbGroup_id.Text = "Please Select Group";
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (upStd_openFileDialog.ShowDialog() == DialogResult.OK)
            {
                txtFileLocation.Text = upStd_openFileDialog.FileName;
            }
        }

        private void btnUploadmarks_Click(object sender, EventArgs e)
        {
            if (cmbGroup_id.Text.Equals("Humanities"))
              Subj_Humanities();
            if (cmbGroup_id.Text.Equals("Science"))
                Subj_Science();
            if (cmbGroup_id.Text.Equals("Business Studies"))
                Subj_Business();

            if (cmbGroup_id.Text.Equals("Humanities Optional"))
                Subj_Humanities_Optional();
            


        }

        private void Subj_Humanities()
        {
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from std_subj", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                MySqlCommand Cmd_insert = null;
                String insert_subj_sql = "";
                string student_id = "";
                string academic_session = "";
                string group_id = cmbGroup_id.Text;
                string subj = "";
                
                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    subj = row[2].ToString();


                    insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                           + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                       + ")";
                    Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                    Cmd_insert.ExecuteNonQuery();

                    subj = row[3].ToString();
                    insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                           + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                       + ")";
                    Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                    Cmd_insert.ExecuteNonQuery();

                    subj = row[4].ToString();
                    insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                           + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                       + ")";
                    Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                    Cmd_insert.ExecuteNonQuery();

                    subj = row[5].ToString();
                    if (!subj.Equals("") && !subj.Equals(" "))
                    {
                        insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                        Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();
                    }

                    subj = row[6].ToString();
                    if (!subj.Equals("") && !subj.Equals(" "))
                    {
                        insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                        Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();
                    }

                    subj = row[7].ToString();
                    if (!subj.Equals("") && !subj.Equals(" "))
                    {
                        insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                        Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();
                    }

                    subj = row[8].ToString();
                    if (!subj.Equals("") && !subj.Equals(" "))
                    {
                        insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                        Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();
                    }

                    subj = row[9].ToString();
                    if (!subj.Equals("") && !subj.Equals(" "))
                    {
                        insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                        Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();
                    }

                    subj = row[10].ToString();
                    if (!subj.Equals("") && !subj.Equals(" "))
                    {
                        insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                        Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();
                    }

                    subj = row[11].ToString();
                    insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id)"
                               + " values('" + student_id + "','" + academic_session + "','" + subj + "'"
                           + ")";
                    Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                    Cmd_insert.ExecuteNonQuery();
                  

                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");
        }
        private void Subj_Science()
        {
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from std_subj", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                MySqlCommand Cmd_insert = null;
                MySqlCommand Cmd_update = null;
                String insert_subj_sql = "";
                String update_subj_sql = "";

                insert_subj_sql = "insert into subj_marks_detail(student_id,academic_session,subject_id )( "
				                  +"  select student_id,academic_session,subject_id from student st,subjects su"
				                  +"  where academic_session='"+cmb_academic_session.Text+"'"
				                  +"  and st.group_id='Science'"
				                  +"  and st.group_id=su.group_id"
				                  +"  order by student_id,subject_id "
				                  +" )";
                Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                Cmd_insert.ExecuteNonQuery();

                string student_id = "";
                string academic_session = "";
                string group_id = cmbGroup_id.Text;
                string bio = "";
                string math = "";

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    bio = row[2].ToString();
                    math = row[3].ToString();
                    string subject_id="";
                    if(bio.Equals("Opt")||bio.Equals("opt"))
                     subject_id="bio";
                     if(math.Equals("Opt")||math.Equals("opt"))
                          subject_id="math";

                     update_subj_sql = " Update subj_marks_detail set is_opt='1'"
                                    + " where student_id='" + student_id + "'"
                                    + " and academic_session='" + academic_session + "'"
                                    + " and subject_id='" + subject_id + "'";
                     Cmd_update = new MySqlCommand(update_subj_sql, connMysql);
                     Cmd_update.ExecuteNonQuery();
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");
        }

        private void Subj_Business()
        {
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from std_subj", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                MySqlCommand Cmd_insert = null;
               // MySqlCommand Cmd_update = null;
                String insert_subj_sql = "";
                //String update_subj_sql = "";

                insert_subj_sql = "insert into subj_marks_detail(student_id,academic_session,subject_id )( "
                                  + "  select student_id,academic_session,subject_id from student st,subjects su"
                                  + "  where academic_session='" + cmb_academic_session.Text + "'"
                                  + "  and st.group_id='Business Studies'"
                                  + "  and su.is_core='1' "
                                  + "  and st.group_id=su.group_id"
                                  + "  order by student_id,subject_id "
                                  + " )";
                Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                Cmd_insert.ExecuteNonQuery();

                string student_id = "";
                string academic_session = "";
                string group_id = cmbGroup_id.Text;
                string sta = "";
                string eco = "";

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    sta = row[2].ToString();
                    eco = row[3].ToString();
                    string subject_id = "";
                    if (sta.Equals("Opt") || sta.Equals("opt"))
                        subject_id = "sta";
                    if (eco.Equals("Opt") || eco.Equals("opt"))
                        subject_id = "eco";

                    insert_subj_sql = " insert into subj_marks_detail( student_id,academic_session, subject_id,is_opt)"
                               + " values('" + student_id + "','" + academic_session + "','" + subject_id + "','1'"
                           + ")";
                    Cmd_insert = new MySqlCommand(insert_subj_sql, connMysql);
                    Cmd_insert.ExecuteNonQuery();

                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");
        }
        private void Subj_Humanities_Optional()
        {
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from std_subj", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
               
                MySqlCommand Cmd_update = null;

                String update_subj_sql = "";


                string student_id = "";
                string academic_session = "";
                string group_id = cmbGroup_id.Text;
                string log = "";
                string geo = "";
                string his = "";
                string psy = "";

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    log = row[2].ToString();
                    geo = row[3].ToString();
                    his = row[4].ToString();
                    psy = row[5].ToString();

                    string subject_id = "";
                    if (log.Equals("Opt") || log.Equals("opt"))
                        subject_id = "log";
                    if (geo.Equals("Opt") || geo.Equals("opt"))
                        subject_id = "geo";
                    if (his.Equals("Opt") || his.Equals("opt"))
                        subject_id = "his";
                    if (psy.Equals("Opt") || psy.Equals("opt"))
                        subject_id = "psy";

                    update_subj_sql = " Update subj_marks_detail set is_opt='1'"
                                   + " where student_id='" + student_id + "'"
                                   + " and academic_session='" + academic_session + "'"
                                   + " and subject_id='" + subject_id + "'";
                    Cmd_update = new MySqlCommand(update_subj_sql, connMysql);
                    Cmd_update.ExecuteNonQuery();

                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");

        }
    }
}
